package com.cybersoft.jdbc.gavin.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.cybersoft.jdbc.gavin.dao.IStudentDao;
import com.cybersoft.jdbc.gavin.entity.Student;
import com.cybersoft.jdbc.gavin.util.BaseDao;

/**
 * Purpose: 
 * @author cybersoft
 * @since  JDK 1.5
 * @date   2013/4/11
 * @MaintenancePersonnel cybersoft
 */
public class StudentDao extends BaseDao implements IStudentDao {
	
	/**
	 * (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#dropTable(java.lang.String)
	 */
	@Override
	public void dropTable(String tableName) {
		// TODO Auto-generated method stub
		String sql = "drop table " + tableName;
		this.execQuery(sql, null);
	}
	
	/**
	 * Constructor:
	 */
	public StudentDao() {
	}
	
	/**
	 * Purpose:建表
	 * @return void
	 */
	@Override
	public void createTable(){
		String sql = "create table SEVEN.Student_gavin(stuname NVARCHAR2(20)" +
					",stuenname NVARCHAR2(20),stusex NVARCHAR2(10)" +
					",stuage number(10),stubrithday DATE,stuemall NVARCHAR2(100)" +
					",stutelephone NVARCHAR2(200),stuheight number(5,2) )";
		
		
		this.execQuery(sql, null);
	 
	}
	
	/** (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#addStudent(com.cybersoft.jdbc.gavin.entity.Student)
	 */
	@Override
	public int addStudent(Student stu) {
		Object[] para = new Object[8];
		String strSql =  "insert into SEVEN.Student_gavin values(?,?,?,?,?,?,?,?)";
		para[0] = stu.getStrStuName();
		para[1] = stu.getStrStuEnName();
		para[2] = stu.getStrStuSex();
		para[3] = stu.getIntStuAge();
		para[4] = stu.getDateStuBrithday();
		para[5] = stu.getStrStuEmail();
		para[6] = stu.getStrStuTelephone();
		para[7] = stu.getDStuHeight();
		
		
		return this.execUpdate(strSql, para);
	}

	/** (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#delStudentByName(java.lang.String)
	 */
	@Override
	public int delStudentByName(String strStuName) {
		Object[] para = {strStuName};
		 
		String strSql = "delete from SEVEN.Student_gavin where stuname = ?";
		return this.execUpdate(strSql, para);
	}

	/** (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#getAllStudent()
	 */
	@Override
	public List getAllStudent() {
		List<Student> stuList = new ArrayList<Student>();
		Student stu ;
		ResultSet res = this.execQuery("select * from SEVEN.Student_gavin", null);
		
		try {
			while(res.next()){
				stu = new Student();
				
				stu.setStrStuName(res.getString(1));
				stu.setStrStuEnName(res.getString(2));
				stu.setStrStuSex(res.getString(3));
				stu.setIntStuAge(res.getInt(4));
				stu.setDateStuBrithday(res.getDate(5));
				stu.setStrStuEmall(res.getString(6));
				stu.setStrStuTelephone(res.getString(7));
				stu.setDStuHeight(res.getFloat(8));
				
				stuList.add(stu);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return stuList;
	}

	/** (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#getStudentByName(java.lang.String)
	 */
	@Override
	public Student getStudentByName(String strStuName) {
		Object[] para = {strStuName} ;
		ResultSet res = this.execQuery("select * from SEVEN.Student_gavin where stuname = ?", para);
		Student stu = new Student();
		
		try {
			while(res.next()){
				stu.setStrStuName(res.getString(0));
				stu.setStrStuEnName(res.getString(1));
				stu.setStrStuSex(res.getString(2));
				stu.setIntStuAge(res.getInt(3));
				stu.setDateStuBrithday(res.getDate(4));
				stu.setStrStuEmall(res.getString(5));
				stu.setStrStuTelephone(res.getString(6));
				stu.setDStuHeight(res.getFloat(7));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return stu;
	}

	/** (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#updateStudent(com.cybersoft.jdbc.gavin.entity.Student)
	 */
	@Override
	public int updateStudent(Student student) {
		 
 		
 		String strSql = "update SEVEN.Student_gavin set stuname = ?,stuenname = ?,stusex = ?" +
 				",stuage = ?,stuemall = ?,stubrithday = ?,stutelephone = ?  where stuname = '"+student.getStrStuName()+ "'"; 
 		
 		Object[] para = {student.getStrStuName(),student.getStrStuEnName(),student.getStrStuSex(),
 				student.getIntStuAge(),student.getStrStuEmail(),student.getDateStuBrithday(),
 				student.getStrStuTelephone() };
		
	// 	String strSql =  "update SEVEN.Student_gavin set stuname = '屈沛',stuenname = 'qu' where stuname = '屈沛' " ;
		
		/*para[0] = student.getStrStuName();
		para[1] = student.getStrStuEnName();
	 	para[2] = student.getStrStuSex();
		para[3] = student.getIntStuAge();
		para[4] = student.getDateStuBrithday();
		para[5] = student.getStrStuEmall();
		para[6] = student.getStrStuTelephone();
		para[7] = student.getFStuHeight(); */
		
		return this.execUpdate(strSql, para);
		
	}
	
	/**
	 * (non-Javadoc)
	 * @see com.cybersoft.jdbc.gavin.dao.IStudentDao#init()
	 */
	@Override
	public void init() {
		// TODO Auto-generated method stub
		
		this.addStudent(new Student("康飞","coffee", "男", 23, java.sql.Date.valueOf("1990-01-01"), 170, "15129248386", "280757544@qq.com"));
		this.addStudent(new Student("杜哲","vinent", "男", 23, java.sql.Date.valueOf("1990-01-01"), 170, "15029592830", "lxq89611@163.com"));
		this.addStudent(new Student("刘晓奇","mars", "男", 23, java.sql.Date.valueOf("1990-01-01"), 173, "15029592830", "lxq89611@163.com"));
		
		
	}

 

}
